﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="QueryRunner.cs" company="Jared Schlicht">
//   Copyright (c) 2011 Jared Schlicht.
// </copyright>
// <summary>
//   Establishes a connection to a SQL Server, runs SQL queries, and returns their results.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace JSchlicht.SqlCsv
{
    using System;
    using System.Collections.Generic;
    using System.Data.Common;

    /// <summary>
    /// Establishes a connection to a SQL Server, runs SQL queries, and returns their results.
    /// </summary>
    public class QueryRunner : IDisposable
    {
        #region Constants and Fields

        /// <summary>
        /// Used to connect to the SQL database.
        /// </summary>
        private readonly DbConnection connection;

        /// <summary>
        /// Specifies the number of seconds that a sql command is allowed to execute before it times out.
        /// </summary>
        private readonly int timeout;

        #endregion

        #region Constructors and Destructors

        /// <summary>
        /// Initializes a new instance of the <see cref="QueryRunner"/> class.
        /// </summary>
        /// <param name="connection">The database connection to use.</param>
        /// <param name="timeout">
        /// The time (in seconds) that a query is allowed to run before it times out.</param>
        public QueryRunner(DbConnection connection, int timeout)
        {
            this.connection = connection;
            this.timeout = timeout;

            if (this.connection == null)
            {
                throw new InvalidOperationException("Unable to open a connection");
            }

            this.connection.Open();
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            this.connection.Close();
        }

        /// <summary>
        /// Runs the specified SQL Query.
        /// </summary>
        /// <param name="query">
        /// The query to run.
        /// </param>
        /// <returns>
        /// A lazy enumerable of string arrays, where each string array is a row from the database. The first string
        /// array returned will contain the column headers.
        /// </returns>
        public IEnumerable<string[]> RunQuery(string query)
        {
            /* This method uses the "yield" keyword to return rows in a lazy manner. Only the current row needs to
             * remain in memory. I could have returned a list of records instead, but it would require the entire
             * set of data to be loaded into memory at one time. This should allow large data sets to be returned in a
             * very efficient manner.
             */
            
            using (DbCommand command = this.connection.CreateCommand())
            {
                command.CommandText = query;
                command.CommandTimeout = this.timeout;

                using (DbDataReader reader = command.ExecuteReader())
                {
                    // Write column names first
                    string[] columnNames = new string[reader.FieldCount];
                    for (int i = 0; i < columnNames.Length; i++)
                    {
                        columnNames[i] = reader.GetName(i);
                    }

                    yield return columnNames;

                    // Write data rows next
                    while (reader.Read())
                    {
                        string[] row = new string[reader.FieldCount];
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            row[i] = reader.GetValue(i).ToString();
                        }

                        yield return row;
                    }
                }
            }
        }

        #endregion
    }
}